/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Image;
import dto.Product;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import util.IdbUtil;
import util.StringUtil;
import util.Utils;

/**
 *
 * @author SonLK60201
 */
public class ProductDAO extends BaseDAO<Product> {

    public ProductDAO(IdbUtil _database) {
        super(_database);
    }

    @Override
    public Collection<Product> getAll() throws SQLException {
           /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Product> products = new ArrayList<Product>();
        /** End declare variables **/
        try {
            String queryString = "SELECT * FROM Product";
            connection = _database.getConnection();
            statement = connection.prepareStatement(queryString);
            resultSet = statement.executeQuery();

            Product tempProduct;

            while (resultSet.next()) {
                tempProduct = new Product();
                tempProduct = mappingEntity(tempProduct, resultSet);
                products.add(tempProduct);
            }

        } catch (SQLException ex) {
            throw ex;
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                throw ex;
            }
        }
        return products;
    }

    @Override
    public Product get(int id) throws SQLException {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "select * from product join [image] on product.id = [image].product_id "
                + "where product.id = ?";
        con = _database.getConnection();

        stm = con.prepareStatement(sql);
        stm.setInt(1, id);
        rs = stm.executeQuery();

        Product product = new Product();
        Image imgDTO;
        boolean is_first = true;
        while (rs.next()) {
            if (is_first) {
                product = new Product(rs.getInt("id"), rs.getString("name"), rs.getString("description"), rs.getFloat("price"),
                        rs.getInt("quantity"), rs.getDate("create_date"), rs.getDate("update_date"),
                        rs.getBoolean("is_deleted"), rs.getBoolean("is_showed"));
                is_first = false;
            }
            imgDTO = new Image(rs.getInt(11), rs.getString(13), rs.getString(14), rs.getString(15), rs.getBoolean(16));
            product.getImages().add(imgDTO);
        }
        rs.close();
        stm.close();
        con.close();
        return product;
    }

    @Override
    public Product create(Product entity) throws SQLException {
         /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            String queryString = "INSERT INTO [dbo].[Product] VALUES(?,?,?,?,?,?,?,?,?)";
            connection = _database.getConnection();

            statement = connection.prepareStatement(queryString);
            statement.setInt(1, entity.getCategory().getId());
            statement.setString(2, entity.getName());
            statement.setString(3, entity.getDescription());
            statement.setFloat(4, entity.getPrice());
            statement.setInt(5, entity.getQuantity());
            statement.setDate(6, Utils.convertToSqlDate(entity.getCreate_date()));
            statement.setDate(7, Utils.convertToSqlDate(entity.getUpdate_date()));
            statement.setBoolean(8, entity.isIs_deleted());
            statement.setBoolean(9, entity.isIs_showed());

            statement.execute();
        }catch (SQLException ex) {
            throw ex;
        }finally {
            try {
                if (resultSet != null)
                {
                   resultSet.close();
                }
                if (statement != null)
                {
                   statement.close();
                }
                if (connection != null)
                {
                   connection.close();
                }
            } catch (SQLException ex) {
             throw ex;
            }
        }
        return entity;
    }

    @Override
    public void update(Product entity) throws SQLException {
            /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        /** End declare variables **/
        try {
            StringBuilder queryString = new StringBuilder();
            queryString.append("UPDATE [dbo].[Product] SET");
            queryString.append(" [category_id] = ?");
            queryString.append(" [name] = ?");
            queryString.append(",[description] = ?");
            queryString.append(",[price] = ?");
            queryString.append(",[quantity] = ?");
            queryString.append(",[create_date]] = ?");
            queryString.append(",[update_date] = ?");
            queryString.append(",[is_deleted] = ?");
            queryString.append(",[is_showed] = ?");
            queryString.append(" WHERE [dbo].[Product].[id] = ?");
            connection = _database.getConnection();
            statement = connection.prepareStatement(queryString.toString());

            statement.setInt(1, entity.getCategory().getId());
            statement.setString(2, entity.getName());
            statement.setString(3, entity.getDescription());
            statement.setFloat(4, entity.getPrice());
            statement.setInt(5, entity.getQuantity());
            statement.setDate(6, Utils.convertToSqlDate(entity.getCreate_date()));
            statement.setDate(7, Utils.convertToSqlDate(entity.getUpdate_date()));
            statement.setBoolean(8, entity.isIs_deleted());
            statement.setBoolean(9, entity.isIs_showed());
            statement.setInt(10, entity.getId());
             
            statement.execute();
        }catch(SQLException ex){
            throw ex;
        }finally {
            try {
                if (resultSet != null)
                {
                   resultSet.close();
                }
                if (statement != null)
                {
                   statement.close();
                }
                if (connection != null)
                {
                   connection.close();
                }
            } catch (SQLException ex) {
              throw ex;
            }
        }
    }

    @Override
    public boolean delete(int id) throws SQLException {
         boolean result = false;
       /** Declare variables to connect to DB**/
        Connection connection = null;
        PreparedStatement statement = null;

       /** End declare variables **/
        try {
            String queryString = "DELETE FROM [dbo].[Product] WHERE [dbo].[Product].id = ?";

            connection = _database.getConnection();
            statement = connection.prepareStatement(queryString.toString());

            statement.setInt(1, id);

            result = statement.execute();
        } catch (SQLException ex) {
            throw ex;
        }
        return result;
    }

    /**
     * Get latest product for showing in index page.
     * @return latestProducts Collection of products.
     */
    public Collection<Product> getLastestProducts() {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            List<Product> latestProducts = new ArrayList<Product>();
            String sql = "select * from product left join [image] on product.id = [image].product_id "
                    + "where is_showed = 1 "
                    + "order by product.id";
            con = _database.getConnection();

            stm = con.prepareStatement(sql);
            rs = stm.executeQuery();

            Product latestProduct;
            Image imgDTO;
            int imageId;
            int currentProductId = 0;
            int lastProductId = 0;
            while (rs.next()) {
                currentProductId = rs.getInt("id");
//                imgDTO = new Image(rs.getInt(11), rs.getString(13), rs.getString(14), rs.getString(15));

                // new product
                if (currentProductId != lastProductId) {

                    latestProduct = new Product(rs.getInt("id"), rs.getString("name"), rs.getString("description"), rs.getFloat("price"),
                            rs.getInt("quantity"), rs.getDate("create_date"), rs.getDate("update_date"),
                            rs.getBoolean("is_deleted"), rs.getBoolean("is_showed"));

                    // check that product have image or not
                    imageId = rs.getInt(11);
                    if (imageId != 0) { // add image to product
                        imgDTO = new Image(rs.getInt(11), rs.getString(13), rs.getString(14), rs.getString(15), rs.getBoolean(16));
                        latestProduct.getImages().add(imgDTO);
                    }

                    latestProducts.add(latestProduct);
                } else { // still product, just add image
                    imgDTO = new Image(rs.getInt(11), rs.getString(13), rs.getString(14), rs.getString(15), rs.getBoolean(16));
                    latestProducts.get(latestProducts.size() - 1).getImages().add(imgDTO);
                }


                lastProductId = currentProductId;
            }
            return latestProducts;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

    }

    public Collection<Product> getProductsOfCategory(int categoryId) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;
        try {
            List<Product> latestProducts = new ArrayList<Product>();
            String sql = "select * from product left join [image] on product.id = [image].product_id "
                    + "where is_showed = 1 and category_id = ? "
                    + "order by product.id";
            con = _database.getConnection();

            stm = con.prepareStatement(sql);
            stm.setInt(1, categoryId);
            rs = stm.executeQuery();

            Product latestProduct;
            Image imgDTO;
            int imageId;
            int currentProductId = 0;
            int lastProductId = 0;
            while (rs.next()) {
                currentProductId = rs.getInt("id");
//                imgDTO = new Image(rs.getInt(11), rs.getString(13), rs.getString(14), rs.getString(15));

                // new product
                if (currentProductId != lastProductId) {

                    latestProduct = new Product(rs.getInt("id"), rs.getString("name"), rs.getString("description"), rs.getFloat("price"),
                            rs.getInt("quantity"), rs.getDate("create_date"), rs.getDate("update_date"),
                            rs.getBoolean("is_deleted"), rs.getBoolean("is_showed"));

                    // check that product have image or not
                    imageId = rs.getInt(11);
                    if (imageId != 0) { // add image to product
                        imgDTO = new Image(rs.getInt(11), rs.getString(13), rs.getString(14), rs.getString(15), rs.getBoolean(16));
                        latestProduct.getImages().add(imgDTO);
                    }

                    latestProducts.add(latestProduct);
                } else { // still product, just add image
                    imgDTO = new Image(rs.getInt(11), rs.getString(13), rs.getString(14), rs.getString(15), rs.getBoolean(16));
                    latestProducts.get(latestProducts.size() - 1).getImages().add(imgDTO);
                }


                lastProductId = currentProductId;
            }
            return latestProducts;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stm != null) {
                    stm.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    private Product mappingEntity(Product tempProduct, ResultSet resultSet) throws SQLException {

        tempProduct.setId(resultSet.getInt(StringUtil.Id));
        tempProduct.setName(resultSet.getString("name"));
        tempProduct.setDescription(resultSet.getString("description"));
        tempProduct.setPrice(resultSet.getFloat("price"));
        tempProduct.setQuantity(resultSet.getInt("quantity"));
        tempProduct.setCreate_date(resultSet.getDate(StringUtil.CreatedDate));
        tempProduct.setUpdate_date(resultSet.getDate(StringUtil.UpdatedDate));
        tempProduct.setIs_deleted(resultSet.getBoolean(StringUtil.IsDeleted));
        tempProduct.setIs_showed(resultSet.getBoolean("is_showed"));

        return tempProduct;
    }
}
